package na.learn.java.dao.dummy;


import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Savepoint;
import java.sql.Statement;

/**
 * sql>select A.CREDIT_CARD_ID, A. TYPE, B.OWNER "B_Owner", B.CREATED "B_Created
 " from credit_card A inner join billing_details B on A.CREDIT_CARD_ID = B.BILLIN
 G_DETAIL_ID;
 
 
 mysql> select A.*,B.owner from bank_account A left outer join billing_details B
 on A.bank_account_id = B.billing_detail_id;
 
 mysql>  create procedure getDetails( IN billingid bigint(20)) select * from bill
ing_details where billing_detail_id = billingid;
 * @author nagrawal2
 *
 */
public class FetchData {
	Connection con;

	Statement stmt;

	public FetchData() {
		try {
			//Class.forName("org.gjt.mm.mysql.Driver");
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/TABLEPERSUBCLASS?ultraDevHack=true&useUltraDevWorkAround=true", "root",
					"admin");
			stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_UPDATABLE);
			con.setAutoCommit(false);

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	/**
	 * @param args
	 */
	public static void main(String[] args) {

		FetchData dbFetch = new FetchData();
		//dbFetch.queryStatement();
		//dbFetch.updatePrepareStatement(1234, "Nishant");

		//dbFetch.updateStatement();
		//dbFetch.queryStatement();
		dbFetch.callableStmt();
	}

	private void updatePrepareStatement(int num, String name) {
		PreparedStatement prepStmt;
		try {
			prepStmt = con
					.prepareStatement("update billing_details set owner = ? where userid = ?");
			prepStmt.setString(1, name);
			prepStmt.setInt(2, num);
			prepStmt.executeUpdate();

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public void callableStmt() {
		System.out.println("In callableStmt");
		ResultSet srs;

		try {
			//String sql = "{ call getDetails(?)}";
			/*CallableStatement callStmt = con
					.prepareCall(sql, ResultSet.TYPE_SCROLL_SENSITIVE,
							ResultSet.CONCUR_UPDATABLE);*/
			CallableStatement callStmt = con.prepareCall("{call getDetails(?)}");
			callStmt.setInt("",4322);
			srs = callStmt.executeQuery();
			while (srs.next()) {
				int id = srs.getInt("BILLING_DETAIL_ID");
				String owner = srs.getString("OWNER");
				System.out.println("Id: " + id + ", owner: " + owner);
				

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public void queryStatement() {
		ResultSet srs;
		
		try {

			srs = stmt.executeQuery("SELECT * FROM billing_details");
			int counter = 0;
			while (srs.next()) {
				int id = srs.getInt("BILLING_DETAIL_ID");
				String owner = srs.getString("OWNER");
				System.out.println("Id: " + id + ", owner: " + owner);
				++counter;

			}
			System.out.println("Num of rows: " + counter);
			//srs.close();

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public void updateStatement() {
		try {
		//	Savepoint svpt1 = con.setSavepoint("SAVEPOINT_1");

			ResultSet srs = stmt.executeQuery("SELECT * FROM billing_details");
			int counter = 0;

			srs = stmt.executeQuery("SELECT * FROM billing_details");
			counter = 0;
			while (srs.next()) {
				int id = srs.getInt("BILLING_DETAIL_ID");
				String owner = srs.getString("OWNER");
				System.out.println("Id: " + id + ", owner: " + owner);
				srs.updateString("OWNER", owner + id);
				srs.updateRow();
				++counter;

			}
			System.out.println("Num of rows: " + counter);
		//	con.rollback(svpt1);

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}
